# coding:utf-8

from string import Template
from pyparsing import CaselessKeyword, Word, alphas, alphanums, \
                     delimitedList,Group, oneOf, Optional, Suppress, \
                     Literal, OneOrMore, quotedString, nums, Forward, \
                     ZeroOrMore, Token

from sql_rice.utils import flatten

SELECT, INSERT, UPDATE, DELETE, FROM, WHERE = [CaselessKeyword(kword) \
     for kword in ('select','insert', 'update', 'delete', 'from', 'where')]


IDENTIFIER = Word(alphas, alphanums + '_$')
PARAM = Word(':', alphas + alphanums)('param')
COLUMN = delimitedList(IDENTIFIER, '.', combine=True)
COLUMN_LIST = Group(delimitedList(COLUMN))
TABLE= delimitedList(IDENTIFIER, '.', combine=True)
TABLE_LIST = Group(delimitedList(TABLE))
BINOP = oneOf('= != < > <= >=')
AND = CaselessKeyword('and')
OR = CaselessKeyword('or')
NOT = CaselessKeyword('not')
IN =  CaselessKeyword('in')
INTEGER = Word(nums)
VALUE  = INTEGER | quotedString | COLUMN
QUERY = Forward()
WHERE_EXPRESION = Forward()

CONDICION = Group((COLUMN + BINOP + PARAM) |
                  (COLUMN + IN + '(' + delimitedList(VALUE) + ')') |
                  (COLUMN + IN + '(' + QUERY + ')') |
                  ('(' + WHERE_EXPRESION + ')')
                  )
                  
WHERE_EXPRESION << CONDICION + ZeroOrMore((AND | OR) + WHERE_EXPRESION )


QUERY << SELECT + COLUMN_LIST.setResultsName('columns') + \
        FROM + TABLE_LIST.setResultsName('tables') + \
        Optional(WHERE + WHERE_EXPRESION)('where')



class SqlParser(object):
    
    UPDATE_TEMPLATE = Template('UPDATE ${table} SET ${fields} WHERE ${old_fields}')
    DELETE_TEMPLATE = Template('DELETE FROM ${table} WHERE ${params}')
    INSERT_TEMPLATE = Template('INSERT INTO ${table}(${fields}) VALUES(${params})')
    
    def __init__(self, sql, **kwargs):
        tokens = QUERY.parseString(sql)
        self.tables = tokens.tables.asList()
        self.fields = tokens.columns.asList()
        try:
            self.params = list(set(t[1:] for t in \
                flatten(tokens.where.asList()) if t.startswith(':')))
        except AttributeError:
            self.params = []
    
    @property
    def update_query(self):
        fields = ', '.join([f + '=:' + f for f in self.fields])
        old_fields = ' AND '.join([f + '=:old_' + f for f in self.fields])
        return self.UPDATE_TEMPLATE.substitute(table=self.tables[0], \
                    fields=fields, old_fields=old_fields)
    
    @property
    def delete_query(self):
       params = ' AND '.join([f + '=:' + f for f in self.fields])
       return self.DELETE_TEMPLATE.substitute(table=self.tables[0], \
                    params=params)
    
    @property
    def insert_query(self):
        fields = ', '.join(self.fields)
        params = ', '.join([':' + f for f in self.fields])
        return self.INSERT_TEMPLATE.substitute(table=self.tables[0], \
                    fields=fields, params=params)
